package pos.data;
import java.sql.*;

import java.util.*;
import java.sql.SQLException;
import pos.domain.*;
//import com.mysql.jdbc.PreparedStatement;
import pos.utils.UIDGenerator;



  public class JDBCExchangeDAO implements IExchangeDAO{
    
      
       
     
	
         public List<Exchange> selectReceiveds(String emailReceived) {
        	 
         Connection conn = ConnectionManager.getInstance().checkOut();

         PreparedStatement stmt = null;
         List<Exchange> recibidos = new LinkedList<Exchange>();
         ResultSet result = null;

         try {

             String sql = "SELECT * FROM Exchange WHERE (emailReceived= ?) AND (state= ?)";
             stmt =conn.prepareStatement(sql);             
             stmt.setString(1, emailReceived);
             stmt.setString(2, "EnProceso");
             result = stmt.executeQuery();
             
             while (result.next()) {
            	 	
                 Exchange temp = new Exchange();
                 temp.setExchangeID(result.getString("exchangeID"));
                 temp.setPropertySentID(result.getString("propertySentID"));
                 temp.setPropertyReceivedID(result.getString("propertyReceivedID"));
                 temp.setStartDateSent(result.getString("startDateSent"));
                 temp.setEndDateSent(result.getString("endDateSent"));
                 
                 temp.setStartDateReceived(result.getString("startDateReceived"));
                 temp.setEndDateReceived(result.getString("endDateReceived"));
                 temp.setState(result.getString("state"));
                 temp.setEmailSent(result.getString("emailSent"));
                 temp.setEmailReceived(result.getString("emailReceived"));
                 recibidos.add(temp);
             }
         } catch (SQLException e) {
             System.out.println("Message: " + e.getMessage());
             System.out.println("SQLState: " + e.getSQLState());
             System.out.println("ErrorCode: " + e.getErrorCode());
         } finally {
             ConnectionManager.getInstance().checkIn(conn);
             try {
                 if (result != null)
                     result.close();
                 if (stmt != null)
                     stmt.close();
             } catch (SQLException e) {
             }

         }
         return recibidos;
     }

    
     
     /* public List<Exchange> selectSents(String userOID) {
         Connection conn = ConnectionManager.getInstance().checkOut();

         PreparedStatement stmt = null;
         List<Exchange> enviados = new LinkedList();
         ResultSet result = null;

         try {

             String sql = "SELECT * FROM Exchange WHERE (emailSent= ?) ";
             stmt = (PreparedStatement) conn.prepareStatement(sql); 
             stmt.executeQuery();
             result = stmt.executeQuery();

             while (result.next()) {
                 Exchange temp = new Exchange();
                 temp.setExchangeID(result.getString("exchangeID"));
                 temp.setPropertySentID(result.getString("propertySent"));
                 temp.setPropertyReceivedID(result.getString("propertyReceived"));
                 temp.setStartDateSent(result.getDate("startDateSent"));
                 temp.setEndDateSent(result.getDate("endDateSent"));
                 temp.setStartDateReceived(result.getDate("startDateReceived"));
                 temp.setEndDateReceived(result.getDate("endDateReceived"));
                 temp.setState(result.getString("state"));
                 temp.setEmailSent(result.getString("emailSent"));
                 temp.setEmailReceived(result.getString("emailReceived"));
                 enviados.add(temp);
             }
         } catch (SQLException e) {
             System.out.println("Message: " + e.getMessage());
             System.out.println("SQLState: " + e.getSQLState());
             System.out.println("ErrorCode: " + e.getErrorCode());
         } finally {
             ConnectionManager.getInstance().checkIn(conn);
             try {
                 if (result != null)
                     result.close();
                 if (stmt != null)
                     stmt.close();
             } catch (SQLException e) {
             }

         }
         return enviados;
}
*/
      public void delete(String exchangeID) {
    	  Connection conn = ConnectionManager.getInstance().checkOut();
			String sql= "DELETE FROM Exchange WHERE (exchangeID= ?)";
			PreparedStatement stmt=null;
			
			try {
	            stmt = (PreparedStatement) conn.prepareStatement(sql);
	            stmt.setString(1, exchangeID);
	            stmt.executeUpdate();
	        } catch (SQLException e) {
	            System.err.println("Message: " + e.getMessage());
	            System.err.println("SQLState: " + e.getSQLState());
	            System.err.println("ErrorCode: " + e.getErrorCode());
	        } finally {
	        	ConnectionManager.getInstance().checkIn(conn);
	        	
	            try {
	                if (stmt != null)
	                    stmt.close();
	            } catch (SQLException e) {
	            }
	        }
	    }
 
      
       
   
     
     
    public void update1(String exchangeID) {
    	Connection conn = ConnectionManager.getInstance().checkOut();
			String sql= "UPDATE Exchange SET state=? WHERE (exchangeID= ?)";
			PreparedStatement stmt=null;
			
			try {
	            stmt = (PreparedStatement) conn.prepareStatement(sql);
	            stmt.setString(1, "Aceptado");
	            stmt.setString(2, exchangeID);
	            stmt.executeUpdate();
	        } catch (SQLException e) {
	            System.err.println("Message: " + e.getMessage());
	            System.err.println("SQLState: " + e.getSQLState());
	            System.err.println("ErrorCode: " + e.getErrorCode());
	        } finally {
	        	ConnectionManager.getInstance().checkIn(conn);
	            try {
	                if (stmt != null)
	                    stmt.close();
	            } catch (SQLException e) {
	            }
	        }
	    }
	    	        

 
     
     
     
     public Exchange select(String exchangeID) {
    	 Connection conn = ConnectionManager.getInstance().checkOut();
         PreparedStatement stmt = null;
         ResultSet result = null;
         Exchange ex = null;
         String sql = "SELECT * FROM Exchange WHERE (exchangeID = ?) ";

         try {
             stmt = (PreparedStatement) conn.prepareStatement(sql);
             stmt.setString(1, exchangeID);
            
             result = stmt.executeQuery();

             result.next();
             ex = new Exchange();
             ex.setExchangeID(result.getString("exchangeID"));
             ex.setPropertySentID(result.getString("propertySentID"));
             ex.setPropertyReceivedID(result.getString("propertyReceivedID"));
             ex.setStartDateSent(result.getString("startDateSent"));
             ex.setEndDateSent(result.getString("endDateSent"));
             ex.setStartDateReceived(result.getString("startDateReceived"));
             ex.setEndDateReceived(result.getString("endDateReceived"));
             ex.setState(result.getString("state"));
             ex.setEmailSent(result.getString("emailSent"));
             ex.setEmailReceived(result.getString("emailReceived"));
             
         } catch (SQLException e) {
             System.out.println("Message: " + e.getMessage());
             System.out.println("SQLState: " + e.getSQLState());
             System.out.println("ErrorCode: " + e.getErrorCode());
         } finally {
        	 ConnectionManager.getInstance().checkIn(conn);
             try {
                 if (result != null) {
                     result.close();
                 }
                 if (stmt != null) {
                     stmt.close();
                 }
             } catch (SQLException e) {
             }
         }
         return ex;
     }
     
     
     
    /* public void update2(String startDateSent,String endDateSent,String startDateReceived,String endDateReceived,String state,String exchangeID) {
         Connection conn = ConnectionManager.getInstance().checkOut();
         String sql = "UPDATE Exchange SET  startDateSent= ?, endDateSent = ?, startDateReceived = ?, endDateReceived= ?, state= ? WHERE (exchangeID = ?) ";
         PreparedStatement stmt = null;
  

         try {
             stmt = (PreparedStatement) conn.prepareStatement(sql);

             stmt.setString(1,startDateSent );
             stmt.setString(2, endDateSent);
             stmt.setString(3,startDateReceived);
             stmt.setString(4,endDateReceived);
             stmt.setString(5,state);
             stmt.setString(6,exchangeID);
             stmt.executeUpdate();

         } catch (SQLException e) {
             System.out.println("Message: " + e.getMessage());
             System.out.println("SQLState: " + e.getSQLState());
             System.out.println("ErrorCode: " + e.getErrorCode());
         } finally {
             //cerramos la conexion a la BD
             ConnectionManager.getInstance().checkIn(conn);
             //liberamos la BD
             try {
                 if (stmt != null) {
                     stmt.close();
                 }
             } catch (SQLException e) {
             }
         }

     }

    
     */

     public void insert(Exchange ex){
            
                 Connection conn = ConnectionManager.getInstance().checkOut();
                 String sql = "INSERT INTO Exchange ( exchangeOID, exchangeID, propertySentID, propertyReceivedID, startDateSent, endDateSent, startDateReceived, endDateReceived, state, emailSent, emailReceived ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?) ";
                 PreparedStatement stmt = null;
                 UIDGenerator exchangeOID =UIDGenerator.getInstance();
                 UIDGenerator exchangeID=UIDGenerator.getInstance();
                 try {
                     stmt = (PreparedStatement) conn.prepareStatement(sql);
     	            stmt.setString(1, exchangeOID.getKey());	
                     stmt.setString(2,exchangeID.getKey());
                     stmt.setString(3, ex.getPropertySentID());
                     stmt.setString(4, ex.getPropertyReceivedID());
                     stmt.setString(5, ex.getStartDateSent());
                     stmt.setString(6, ex.getEndDateSent());
                     stmt.setString(7, ex.getStartDateReceived());
                     stmt.setString(8, ex.getEndDateReceived());
                     stmt.setString(9, ex.getState());
                     stmt.setString(10, ex.getEmailSent());
                     stmt.setString(11,ex.getEmailReceived());
                 

                     stmt.executeUpdate();
                    
                 } catch (SQLException e) {
                     System.out.println("Message: " + e.getMessage());
                     System.out.println("SQLState: " + e.getSQLState());
                     System.out.println("ErrorCode: " + e.getErrorCode());
                 } finally {
                	 ConnectionManager.getInstance().checkIn(conn);

                     try {
                         if (stmt != null) {
                             stmt.close();
                         }
                     } catch (SQLException e) {
                     }
                 }
    
		 	 
		 
	 }
 public List<Exchange> selectRecibidosVal(String email) {
    	 
         Connection conn = ConnectionManager.getInstance().checkOut();

         PreparedStatement stmt = null;
         List<Exchange> ResultSearch = new LinkedList<Exchange>();
         ResultSet result = null;

         try {

             String sql = "SELECT * FROM Exchange WHERE (emailReceived= ? OR emailSent= ?) AND (state= ?)";
             stmt =conn.prepareStatement(sql);             
             stmt.setString(1, email);
             stmt.setString(2,email);
             stmt.setString(3, "PorValorar");
             result = stmt.executeQuery();
             
             while (result.next()) {
            	 	
                 Exchange temp = new Exchange();
                 temp.setExchangeID(result.getString("exchangeID"));
                 temp.setPropertySentID(result.getString("propertySentID"));
                 temp.setPropertyReceivedID(result.getString("propertyReceivedID"));
                 temp.setStartDateSent(result.getString("startDateSent"));
                 temp.setEndDateSent(result.getString("endDateSent"));
                 
                 temp.setStartDateReceived(result.getString("startDateReceived"));
                 temp.setEndDateReceived(result.getString("endDateReceived"));
                 temp.setState(result.getString("state"));
                 temp.setEmailSent(result.getString("emailSent"));
                 temp.setEmailReceived(result.getString("emailReceived"));
                 ResultSearch.add(temp);
             }
         } catch (SQLException e) {
             System.out.println("Message: " + e.getMessage());
             System.out.println("SQLState: " + e.getSQLState());
             System.out.println("ErrorCode: " + e.getErrorCode());
         } finally {
             ConnectionManager.getInstance().checkIn(conn);
             try {
                 if (result != null)
                     result.close();
                 if (stmt != null)
                     stmt.close();
             } catch (SQLException e) {
             }

         }
         return ResultSearch;
     }


 public void updateATerminar(String exchangeID) {
 	Connection conn = ConnectionManager.getInstance().checkOut();
			String sql= "UPDATE Exchange SET state=? WHERE (exchangeID= ?)";
			PreparedStatement stmt=null;
			
			try {
	            stmt = (PreparedStatement) conn.prepareStatement(sql);
	            stmt.setString(1, "Terminado");
	            stmt.setString(2, exchangeID);
	            stmt.executeUpdate();
	            
	        } catch (SQLException e) {
	            System.err.println("Message: " + e.getMessage());
	            System.err.println("SQLState: " + e.getSQLState());
	            System.err.println("ErrorCode: " + e.getErrorCode());
	        } finally {
	        	ConnectionManager.getInstance().checkIn(conn);
	            try {
	                if (stmt != null)
	                    stmt.close();
	            } catch (SQLException e) {
	            }
	        }
	    }
     
}
  
  


